***********************************************************************************
********"Macroprudential Regulation, Quantitative Easing, and Bank Lending"********
***********************************************************************************
* This file produces some of the datasets that will be used for the 2015 analyses
***********************************************************************************
***********************************************************************************


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/cbabser, case(lower)
rename ente_segn bankid
sort bankid

tostring(data_oss), replace
gen xxx=date(data_oss, "YMD")
drop data_oss

gen time=mofd(xxx)
drop xxx
format time %tm

rename valore cbabser
format %20.0g cbabser

merge m:1 bankid using temp, keepus(exbdisbur)
keep if _merge==3
drop _merge exbdisbur

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) cbabser, by(bankid time)

keep if time>=tm(2012m8)&time<=tm(2014m8)
egen xxx=max(cbabser), by(bankid)
duplicates drop bankid, force
gen excbabserd0=(xxx>0)
drop time xxx

keep bankid excbabserd0

**# Bookmark (saving dataset - covered bonds and asset backed securities exposure
* dummy as defined in Appendix II)
compress
save $inputdirectory/INB_cbabser2015, replace


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/isins, case(lower)

rename dat time
rename date_mat time_mat
rename ente_segn bankid
rename fonte source
egen isin=group(cod_isin)
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
tostring(time_mat), replace
gen xxx=date(time_mat, "YMD")
rename time_mat oldtime_mat
gen time_mat=mofd(xxx)
drop xxx
format time_mat %tm
replace time_mat=tm(2200m12) if time_mat==tm(9999m12)
gen monthtom=time_mat-time
drop if monthtom<0

replace book=-book if portaf_contab==508|portaf_contab==509
gen portfolio=.
replace portfolio=1 if inlist(portaf_contab,504,505,506,512)
replace portfolio=2 if inlist(portaf_contab,503)
replace portfolio=3 if inlist(portaf_contab,501,502,0,508)
drop if portfolio==.

fillin bankid time portfolio
drop if portfolio==.
replace isin=0 if isin==.
replace book=0 if book==.

egen id=group(isin bankid portfolio)
collapse (sum) book (first) cod_isin isin bankid portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)
xtset id time

merge m:1 bankid time using $inputdirectory/INB_assetsi, keepus(assets)
keep if _merge==3
drop _merge

merge m:1 bankid using temp, keepus(exbdisbur)
keep if _merge==3
drop _merge exbdisbur

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop id
drop bankid
rename gru bankid

egen id=group(isin bankid portfolio)
collapse (sum) book assets (first) cod_isin bankid isin portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)

xtset id time

keep if time==tm(2014m8)
* Keep securities not valued at historical cost
keep if portfolio==3|portfolio==2

gen cbabs=0
replace cbabs=1 if  inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974)&inlist(stanziabilita,23,24)&inlist(codvecom,"400H","420A","420B","420C")

gen book2=book
replace book=0 if cbabs!=1

collapse (sum) book book2 (max) assets, by(bankid)
gen excbabs4=100*(book/assets)

gen excbabs4d0=(excbabs4>0)
keep bankid excbabs4*

**# Bookmark (saving dataset - covered bonds and asset backed securities exposure
* as defined in Appendix II)
compress
save $inputdirectory/INB_cbabs42015, replace

*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/assets, case(lower)

*PRELIMINARY STEPS
rename dat time
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
format assets %20.0g
rename ente_segn bankid
drop oldtime

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) assets, by(bankid time)
xtset bankid time

**# Bookmark (saving dataset - total assets)
compress
save $inputdirectory/INB_assets2015, replace

*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/tltroauction

rename ENTE_SEGN bankid
rename SUM_VALORE_ tltroauction
format tltroauction %20.0g
generate double time=(DATA_OSS*1000)

gen time2=dofc(time)
gen time3=mofd(time2)
format time3 %tm
drop time2 time
rename time3 time
drop DATA_OSS

xtset bankid time 
fillin bankid time
recode tltroauction (.=0)

gen deltatltroauctiondec=tltroauction-L.tltroauction
gen xxx=deltatltroauctiondec if time==tm(2014m9)
egen deltatltroauctionsep=max(xxx), by(bankid)
keep if time==tm(2014m12)
egen deltatltroauctionsepdec=rowtotal(deltatltroauctionsep deltatltroauctiondec)
format deltatltroauction* %20.0g

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid

collapse (sum) deltatltroauction* , by(bankid time)

merge 1:1 bankid time using $inputdirectory/INB_assets2015
keep if _merge==3
drop _merge time

recode deltatltroauction* (.=0)
drop deltatltroauctionsep

gen extdaadec=100*deltatltroauctiondec/assets
gen extdaasepdec=100*deltatltroauctionsepdec/assets

keep bankid extdaa*

**# Bookmark (saving dataset -  amount borrowed in September and December 2014 
* via TLTRO I)
compress
save $inputdirectory/INB_tltroauction2015, replace

*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/ecbdepo, case(lower)

rename dat date
tostring(date), replace
gen xxx=date(date, "YMD")
drop date
gen time=mofd(xxx)
drop xxx
format time %tm
rename ente_segn bankid
drop abi

merge m:1 bankid using $inputdirectory/INB_gru2015, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) ecbdepo, by(bankid time)

**# Bookmark (saving dataset - deposits at the ECB)
compress
save $inputdirectory/INB_ecbdepo2015, replace



